﻿using System;
using System.IO;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Malahit_1
{
    public class MainForm : Form
    {
        private const string CONFIG_FILE = "config.txt";
        private SqlConnection conn; // соединение с БД
        private Panel buttonPanel = new Panel();
        private Panel filterPanel = new Panel();
        private DataGridView dataGridView = new DataGridView();
        private DataSet data = new DataSet();
        private SqlDataAdapter dataAdapter;

        private Label firmnameLabel = new Label();
        private TextBox firmnameTextBox = new TextBox();
        private Button tableButton = new Button();
        public MainForm()
        {
            // Устанавливаем соединение
             if (!File.Exists(CONFIG_FILE))
            {
                MessageBox.Show("Нет файла с конфигурацией подключения к БД","Ошибка",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
             try
             {
                 this.conn = new System.Data.SqlClient.SqlConnection(File.ReadAllText(CONFIG_FILE));
             }
             catch (SqlException ex)
             {
                 MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
            this.Load += new EventHandler(MainForm_Load);
        }

        private void MainForm_Load(System.Object sender, System.EventArgs e)
        {
            SetupLayout();
            SetupDataGridView();
        }

        private void SetupLayout()
        {
            this.Size = new Size(600, 500);
            this.Text = "Тестовое задание 2";
 
            firmnameLabel.Location = new Point(10, 10);
            firmnameLabel.Text = "Название фирмы";
            firmnameTextBox.Location = new Point(110, 10);

            tableButton.Text = "фильтр";
            tableButton.Location = new Point(250, 10);
            tableButton.Click+=new EventHandler(tableButton_Click);

            filterPanel.Controls.Add(firmnameLabel);
            filterPanel.Controls.Add(firmnameTextBox);
            filterPanel.Controls.Add(tableButton);
            filterPanel.Height = 80;
            filterPanel.Dock = DockStyle.Top;

            dataGridView.Dock = DockStyle.Top;
            dataGridView.Height = 350;
            this.Controls.Add(filterPanel);
            this.Controls.Add(dataGridView);

        }
        private void SetupDataGridView()
        {
            try
            {
                dataAdapter = new SqlDataAdapter(@"SELECT DatePart(yy,doc_date) AS 'год',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=1 then sum else null end) AS 'январь',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=2 then sum else null end) AS 'февраль',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=3 then sum else null end) AS 'март',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=4 then sum else null end) AS 'апрель',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=5 then sum else null end) AS 'май',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=6 then sum else null end) AS 'июнь',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=7 then sum else null end) AS 'июль',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=8 then sum else null end) AS 'август',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=9 then sum else null end) AS 'сентябрь',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=10 then sum else null end) AS 'октябрь',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=11 then sum else null end) AS 'ноябрь',
                                    SUM(CASE WHEN DATEPART(m,doc_date)=12 then sum else null end) AS 'декабрь'
                                    FROM Document
                                    GROUP BY  DatePart(yy,doc_date)",conn);
                dataAdapter.Fill(data, "Table");
                dataGridView.DataSource = data;
                dataGridView.DataMember = "Table";
                dataGridView.ReadOnly = true;
                dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Ошибка");
                System.Threading.Thread.CurrentThread.Abort();
            }

        }
        private void tableButton_Click(object sender, EventArgs e)
        {
            /*Фильтрация по названию фирмы*/
            if (firmnameTextBox.Text.Length > 0)
            {
                try
                {
                    dataAdapter = new SqlDataAdapter(@"SELECT DatePart(yy,doc_date) AS 'год',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=1 then sum else null end) AS 'январь',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=2 then sum else null end) AS 'февраль',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=3 then sum else null end) AS 'март',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=4 then sum else null end) AS 'апрель',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=5 then sum else null end) AS 'май',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=6 then sum else null end) AS 'июнь',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=7 then sum else null end) AS 'июль',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=8 then sum else null end) AS 'август',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=9 then sum else null end) AS 'сентябрь',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=10 then sum else null end) AS 'октябрь',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=11 then sum else null end) AS 'ноябрь',
                                        SUM(CASE WHEN DATEPART(m,doc_date)=12 then sum else null end) AS 'декабрь'
                                        FROM Document
                                        INNER JOIN Firm ON Firm.Firm_id=Document.Firm_id
                                        WHERE Firm.Name=@firmname
                                        GROUP BY  DatePart(yy,doc_date)", conn);
                    dataAdapter.SelectCommand.Parameters.Add("@firmname", SqlDbType.VarChar, 255).Value = firmnameTextBox.Text;
                    data.Clear();
                    dataAdapter.Fill(data, "Table");
                    dataGridView.DataSource = data;
                    dataGridView.DataMember = "Table";
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка");
                    System.Threading.Thread.CurrentThread.Abort();
                }
            }
            else
            {
                SetupDataGridView();
            }
        }
    }
}